<?php

namespace Biz\module\Classes\mgt;

use Biz\model\GoodsSku as mGoodsSku;
use Biz\model\trade\CustomizePayWay as mCustomizePayWay;
use Biz\model\trade\Order as mOrder;
use Biz\model\trade\OrderExcel as mOrderExcel;
use Biz\model\trade\OrderExcelLog as mOrderExcelLog;
use Biz\model\trade\OrderGoods as mOrderGoods;
use Biz\model\trade\OrderPackage as mOrderPackage;
use Biz\model\trade\OrderRefund as mOrderRefund;
use Biz\pockets\excel\DownExcel;
use Biz\pockets\excel\ReadExcel;
use Biz\pockets\excel\UpExcel;
use Biz\service\Group;
use Biz\service\trade\Order as sOrder;
use Biz\tools\ShopArea as tArea;
use Doraemon\tools\Log as tLog;


class OrderExcel
{
    private $where = [ //商家未发货状态
        'status' => [mOrder::STATUS_WAIT_SEND, mOrder::STATUS_SEND_PART_OUT]
    ];

    public function __construct($shopId)
    {
        $this->where['shop_id'] = $shopId;
    }

    /**
     * 发货订单下载
     *
     * @throws \Exception
     */
    public function downList($params, $type = 1)
    {
        $array = [];
        $orderGoods = [];
        $start_time = '';
        $end_time = '';
        if (!empty($params['start_time'])) {
            $start_time = date('Y-m-d H:i:s', strtotime($params['start_time']));
            $this->where['pay_time[>=]'] = $start_time;
        }
        if (!empty($params['end_time'])) {
            $end_time = date('Y-m-d H:i:s', strtotime($params['end_time']));
            $this->where['pay_time[<]'] = $end_time;
        }

        //todo 排除拼团中的订单
        $groupOrder = Group::getShopJoinOrder($this->where['shop_id'], $start_time, $end_time);
        if (!empty($groupOrder)) {
            $this->where['order_no[!]'] = $groupOrder;
        }

        $list = mOrder::select('*', $this->where);

        $orderNos = array_unique(array_column($list, 'order_no'));

        if (empty($orderNos)) {
            throw new \Exception('您暂无未发订单', 0);
        }


        $orderGoodsList = mOrderGoods::getFieldsByOrderNo($orderNos, [
            'cover_img', 'title', 'sell_price', 'buy_price', 'buy_num', 'coupon_amount', 'sku_desc', 'order_no', 'sku_id', 'refund_order_no'
        ]);
        $skuIds = array_column($orderGoodsList, 'sku_id');
        $skuList = mGoodsSku::getInfoList($skuIds);
        $skuNos = array_column($skuList, 'sku_no', 'id');

        //退款记录查询
        $refundList = mOrderRefund::getRefundedOrRefundingList($orderNos, 'mgt');
        array_walk($orderGoodsList, function ($v) use (&$orderGoods, &$skuNos, $refundList) {
            $refundInfo = isset($refundList[$v['refund_order_no']]) ? $refundList[$v['refund_order_no']] : [];
            if (isset($refundInfo['status']) && $refundInfo['status'] == mOrderRefund::STATUS_COMPLETE) {
                return;
            }
            //加上退款状态
            $v['refund_v_status_desc'] = '';
            if (!empty($refundInfo)) {
                $v['status_desc'] = $refundInfo['status_desc'];
            }
            $v['sku_no'] = $skuNos[$v['sku_id']];
            $orderGoods[$v['order_no']][] = $v;
        });


        //查询订单的各个备注
        $remarks = sOrder::OrderRemarks($orderNos);


        if ($type == 2) {
            bcscale(2);
            foreach ($list as $key => $order) {
                $packageSkuIds = [];
                if ($order['is_package_delivery'] == mOrder::IS_PACKAGE_DELIVERY_YES) {
                    $packageSkuIds = mOrderPackage::getAllPackageSkuIds($order['order_no']);
                }

                foreach ($orderGoods[$order['order_no']] as $goods) {
                    //如果已经分包裹发货，那么不显示相关的发货记录
                    if (in_array($goods['sku_id'], $packageSkuIds)) {
                        continue;
                    }

                    $orderNoA = explode('-', $order['order_no']);
                    $array[] = [
                        'order_no' => $order['order_no'], //订单号
                        'courier_code' => '', //快递公司编号
                        'express_no' => '', //快递单号
                        'consignee_name' => $order['consignee_name'], //收件人姓名
                        'consignee_phone' => $order['consignee_phone'], //收件人电话
                        'consignee_province' => tArea::locationId2Name($order['consignee_province']), //省
                        'consignee_city' => tArea::locationId2Name($order['consignee_city']), //市
                        'consignee_area' => tArea::locationId2Name($order['consignee_area']), //区县
                        'consignee_address' => $order['consignee_info_address'], //具体地址
                        'message' => $order['message'],//买家留言
                        'seller_message' => isset($remarks[$order['order_no']]['seller_message']) ? $remarks[$order['order_no']]['seller_message'] : '',
                        'hhz_message' => isset($remarks[$order['order_no']]['hhz_message']) ? self::formatHhzMessage($remarks[$order['order_no']]['hhz_message'], 'csv') : '',
                        'title' => $goods['title'] . '-' . $goods['sku_desc'],//订单商品名称
                        'buy_price' => $goods['buy_price'],//订单商品价格
                        'buy_num' => $goods['buy_num'],//订单商品数量
                        'shipping_amount' => $order['shipping_amount'],
                        'pay_amount' => bcsub(bcmul($goods['buy_price'], $goods['buy_num']), $goods['coupon_amount']),
                        'pay_time' => $order['pay_time'],
                        'sku_no' => $goods['sku_no'],//订单商品编码
                        'refund_status_desc' => (isset($goods['status_desc']) && !empty($goods['status_desc'])) ? $goods['status_desc'] : '',
                        'nickname' => '住友_' . $orderNoA[2],
                        'sku_id' => $goods['sku_id']
                    ];
                }
            }
            $header = ['订单号', '快递公司编号', '快递单号', '收件人姓名', '收件人电话', '省份', '市区', '区县', '具体地址', '买家留言', '商家备注', '客服备注', '订单商品名', '商品价格', '商品数量', '运费', '金额', '支付时间', '商品编码', '退款状态', '买家账号', 'SKU_ID'];
            $letter = range('A', 'V');
            $excelObj = new DownExcel();
            $excelObj->setFileName('批量发货');
            $excelObj->setSheetTitle('批量发货订单处理');
            //设置列宽
            $widths = [
                'A' => 50,
                'B' => 30,
                'C' => 50,
                'D' => 30,
                'E' => 50,
                'F' => 20,
                'G' => 20,
                'H' => 20,
                'I' => 50,
                'J' => 50,
                'K' => 30,
                'L' => 20,
                'M' => 20,
                'N' => 20,
                'O' => 20,
                'P' => 20,
                'Q' => 20,
                'R' => 20,
                'S' => 20,
                'T' => 20,
                'U' => 20,
                'V' => 20
            ];
            //设置保护的窗口
            //$freeze = ['A', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K'];
            //设置货币形式
            $excelObj->setMoneyFloatFormat('L');
            $excelObj->setWidth($widths);
            //默认单元格居中
            $excelObj->setDefaultStyleAlignment();
            $excelObj->setTextFormat('B'); //快递编号防止为科学计数法
            $excelObj->setTextFormat('C'); //快递单号防止为科学计数法
            $excelObj->setTextFormat('E'); //收件人电话防止为科学计数法
            $excelObj->setTextFormat('M'); //商品数量防止为科学计数法
            $excelObj->setTextFormat('N'); //运费防止为科学计数法
            $excelObj->setTextFormat('P'); //支付时间防止为科学计数法
            $excelObj->setTextFormat('Q'); //商品编码防止为科学计数法
            $excelObj->setTextFormat('T'); //SKUID防止为科学计数法
            $excelObj->freezePane('A2');//冻结窗口
        }
        foreach ($header as $key => $value) {
            //设置字体
            $excelObj->setFrontName($letter[$key]);
            //设置加粗
            $excelObj->setFrontBold("$letter[$key]1");
            //设置大小
            $excelObj->setFrontSize($letter[$key]);
        }
        if (empty($array)) {
            throw new \Exception('您暂无未发订单', 0);
        }
        $excelObj->create($array, $header);
    }

    /**
     * Excel文件上传
     *
     * @param $file
     *
     * @return string $fileName 文件名
     * @throws \Exception
     */
    public function upLoad($file)
    {
        $upExcel = new UpExcel();
        if ($file = $upExcel->uploadOne($file, $this->where['shop_id'])) {
            return $file;
        } else {
            throw new \Exception(upExcel::$error, 0);
        }
    }

    /**
     * 校检并返回format后的数据
     *
     * @param $fileName
     *
     * @return array
     * @throws \Exception
     */
    public function readAndCheckByFormatExcel($file)
    {
        //字段转换
        $map = [
            '订单号' => 'order_no',
            '快递公司编号' => 'courier_code',
            '快递单号' => 'express_no',
            'SKU_ID' => 'sku_id'
        ];
        $data = (new ReadExcel($file))->getExcelData(0, $map);
        //过滤无用的空行
        $data = array_filter($data, function ($v) {
            if (empty($v['order_no'])) {
                return false;
            }
            if (empty($v['express_no'])) {
                return false;
            }
            return true;
        });
        if (empty($data)) {
            throw new \Exception('没有未发货订单信息', 0);
        }
        $dataCount = count($data);
        if ($dataCount > 1000) {
            throw new \Exception('单次发货最多支持1000笔订单，请修改后重新上传文档');
        }
        $orderNos = array_column($data, 'order_no');

        //对上传数据进行二次处理
        $result = [];
        $checkRepeat = [];
        $repeatOrderSku = [];
        foreach ($data as $orderUp) {
            $search = [" ", "　", "\n", "\r", "\t"];
            $replace = ["", "", "", "", ""];
            $orderUp['express_no'] = str_replace($search, $replace, $orderUp['express_no']);
            $result[$orderUp['express_no']][$orderUp['order_no'] . '--' . $orderUp['sku_id']] = $orderUp;

            if (!isset($checkRepeat[$orderUp['order_no']][$orderUp['sku_id']])) {
                $checkRepeat[$orderUp['order_no']][$orderUp['sku_id']] = true;
            } else {
                $repeatOrderSku[] = $orderUp['order_no'] . '-' . $orderUp['sku_id'];
            }
        }
        if (!empty($repeatOrderSku)) {
            throw new \Exception(sprintf("文档中以下订单号和SKUID重复，请修改后重新上传文档：%s", implode(',', array_values($repeatOrderSku))));
        }
        return $result;
    }

    /**
     * 批量导出订单
     *
     * @param $data
     */
    public function batchExport($data)
    {
        ini_set('memory_limit', '-1');
        bcscale(2);
        $array = [];
        if (empty($data)) {
            return [];
        }
        $orderNos = array_unique(array_column($data, 'order_no'));
        $customizePayList = [];
        $customizePayResult = mCustomizePayWay::select('*', ['order_no' => $orderNos]);
        foreach ($customizePayResult as $row) {
            $payType = $row['pay_type'];
            $customizePayList[$row['order_no']]['pay_way'] = $payType ? mCustomizePayWay::PAY_TYPE_DESC[$payType] : '';
            $customizePayList[$row['order_no']]['pay_no'] = $row['pay_no'];
            $customizePayList[$row['order_no']]['remark'] = $row['remark'];
            $customizePayList[$row['order_no']]['pay_amount'] = $row['pay_amount'];
        }
        //退款记录查询
        $refundList = mOrderRefund::getRefundedOrRefundingList($orderNos, 'mgt');
        foreach ($data as $key => $order) {
            $package_list = [];
            if (isset($order['package_list']) && !empty($order['package_list'])) {
                $package_list = $order['package_list'];
            }
            foreach ($order['goods'] as $_gk => &$goods) {
                $orderNoA = explode('-', $order['order_no']);
                $refundInfo = isset($refundList[$goods['refund_order_no']]) ? $refundList[$goods['refund_order_no']] : [];
                //加上退款状态
                $refundStatusDesc = '';
                if (!empty($refundInfo)) {
                    $refundStatusDesc = $refundInfo['status_desc'];
                }
                $array[$key . $_gk] = [
                    'order_no' => $order['order_no'], //订单号
                    'status' => $order['status_desc'], //订单状态
                    'consignee_province' => tArea::locationId2Name($order['consignee_province']), //省
                    'consignee_city' => tArea::locationId2Name($order['consignee_city']), //市
                    'consignee_area' => tArea::locationId2Name($order['consignee_area']), //区县
                    'consignee_address' => $order['consignee_info_address'], //具体地址
                    'message' => $order['message'],//买家留言
                    'seller_message' => $order['seller_message'],
                    'hhz_message' => self::formatHhzMessage($order['hhz_message']),
                    'title' => $goods['title'] . '-' . $goods['sku_desc'],//订单商品名称
                    'buy_price' => $goods['buy_price'],//订单商品价格
                    'buy_num' => $goods['buy_num'],//订单商品数量
                    'shipping_amount' => $order['shipping_amount'],
                    'pay_amount' => bcsub(bcmul($goods['buy_price'], $goods['buy_num']), $goods['coupon_amount']),
                    'customize_pay_way' => isset($customizePayList[$order['order_no']]) ? $customizePayList[$order['order_no']]['pay_way'] : '',
                    'customize_pay_no' => isset($customizePayList[$order['order_no']]) ? $customizePayList[$order['order_no']]['pay_no'] : '',
                    'customize_remark' => isset($customizePayList[$order['order_no']]) ? $customizePayList[$order['order_no']]['remark'] : '',
                    'pay_time' => $order['pay_time'],
                    'sku_no' => $goods['sku_no'],//订单商品编码
                    'refund_status_desc' => $refundStatusDesc,
                    'nickname' => '住友_' . $orderNoA[2],
                    'delivery_time' => $order['delivery_time'],
                ];
                if (!empty($package_list)) {
                    foreach ($package_list as $_package) {
                        if (in_array($goods['sku_id'], $_package['sku_list'])) {
                            $array[$key . $_gk]['delivery_time'] = $_package['delivery_time'];
                        }
                    }
                }
            }
        }
        $array = array_values($array);
        $header = ['订单号', '订单状态', '省份', '市区', '区县', '具体地址', '买家留言', '商家备注', '好好住备注', '订单商品名', '商品价格', '商品数量', '运费', '金额', '第三方支付方式', '第三方流水单号', '备注', '支付时间', '商品编码', '退款状态', '买家账号', '发货时间'];
        $letter = range('A', 'V');
        $excelObj = new DownExcel();
        $filename = '导出订单' . date('YmdHis');
        $excelObj->setFileName($filename);
        $excelObj->setSheetTitle('导出订单');
        //设置列宽
        $widths = [
            'A' => 50,
            'B' => 30,
            'C' => 30,
            'D' => 30,
            'E' => 50,
            'F' => 20,
            'G' => 20,
            'H' => 20,
            'I' => 30,
            'J' => 20,
            'K' => 20,
            'L' => 20,
            'M' => 20,
            'N' => 20,
            'O' => 35,
            'P' => 35,
            'Q' => 50,
            'R' => 20,
            'S' => 20,
            'T' => 20,
            'U' => 20,
            'V' => 20,
        ];
        //设置保护的窗口
        //$freeze = ['A', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K'];
        //设置货币形式
        $excelObj->setMoneyFloatFormat('K');
        $excelObj->setWidth($widths);
        //默认单元格居中
        $excelObj->setDefaultStyleAlignment();
        $excelObj->setTextFormat('D'); //收件人电话防止为科学计数法
        $excelObj->setTextFormat('J'); //商品数量防止为科学计数法
        $excelObj->setTextFormat('K'); //运费防止为科学计数法
        $excelObj->setTextFormat('M'); //支付时间防止为科学计数法
        $excelObj->setTextFormat('N'); //商品编码防止为科学计数法
        $excelObj->freezePane('A2');//冻结窗口
        foreach ($header as $key => $value) {
            //设置字体
            $excelObj->setFrontName($letter[$key]);
            //设置加粗
            $excelObj->setFrontBold("$letter[$key]1");
            //设置大小
            $excelObj->setFrontSize($letter[$key]);
        }
        $excelObj->create($array, $header);
    }

    private function formatHhzMessage($hhzMessage, $formatType = 'html')
    {
        $format = '';
        if ($hhzMessage) {
            foreach ($hhzMessage as $message) {
                $format .= $message['add_time'] . " " . $message['hhz_remark_type_des'] . "\r\n";
                $format .= $message['content'] . "\r\n";
            }
        }
        if ($formatType == 'html') {
            return str_replace("\r\n", "<br/>", $format);
        } else {
            return str_replace("<br/>", "\r\n", $format);
        }
    }

    public function getOrderSql($orderNos, $orderData)
    {
        $orderSql = 'UPDATE `order` SET';
        $orderSql .= ' status = CASE order_no ';
        foreach ($orderNos as $orderNo) {
            $orderSql .= sprintf("WHEN '%s' THEN %d ", $orderNo, $orderData[$orderNo]['status']);
        }
        $orderSql .= 'END,';
        $orderSql .= ' delivery_time = CASE order_no ';
        foreach ($orderNos as $orderNo) {
            $orderSql .= sprintf("WHEN '%s' THEN '%s' ", $orderNo, $orderData[$orderNo]['delivery_time']);
        }
        $orderSql .= 'END';
        $orderSql .= ' WHERE `order_no` IN (\'' . implode('\',\'', $orderNos) . '\')';
        return $orderSql;
    }


    //增加Error的统计
    public static function addError($id, $msg, $excelLogData = [], $logData = [])
    {
        mOrderExcel::update(['error_count[+]' => 1], ['id' => $id]);

        mOrderExcelLog::createFromData($excelLogData);

        $msg = '批量发货异常' . $msg;

        tLog::order_orderstatus_warning($msg, $logData);
    }
}